/**
 * Title: jFamilyTree SQL provider
 * Description: jFamilyTree SQL provider
 * About: Designed and rewritten by Andreas Lundgren, based on the Treebolic application suite
 * Copyright: (c) 2001-2008
 * Terms of use:see license agreement at http://treebolic.sourceforge.net/en/license.htm
 * Author: Bernard Bou
 * Company: bsys
 */
package jFamilyTree.provider.sql;

import java.awt.Color;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jFamilyTree.model.IEdge;
import jFamilyTree.model.INode;
import jFamilyTree.model.IProvider;
import jFamilyTree.model.IProviderContext;
import jFamilyTree.model.MenuItem;
import jFamilyTree.model.Model;
import jFamilyTree.model.MountPoint;
import jFamilyTree.model.MutableEdge;
import jFamilyTree.model.MutableNode;
import jFamilyTree.model.Settings;
import jFamilyTree.model.Tree;
import jFamilyTree.model.Utils;
import jFamilyTree.model.TreeMutableNode;

/**
 * Provider for SQL
 * 
 * @author Bernard Bou
 */
public class Provider implements IProvider
{
	// S T A T I C S

	/**
	 * Property files
	 */
	static private String thePropertyFile = "jFamilyTreeSql.properties";

	/**
	 * Get nodes SQL statement
	 */
	static private String theDefaultNodesSql = "SELECT * FROM %nodes%;";

	/**
	 * Get tree edges SQL statement
	 */
	static private String theDefaultTreeEdgesSql = "SELECT * FROM %edges% WHERE %istree% = TRUE;";

	/**
	 * Get edges SQL statement
	 */
	static private String theDefaultEdgesSql = "SELECT * FROM %edges% WHERE %istree% = FALSE;";

	/**
	 * Get settings SQL statement
	 */
	static private String theDefaultSettingsSql = "SELECT * FROM %settings%;";

	/**
	 * Get menu item SQL statement
	 */
	static private String theDefaultMenuSql = "SELECT * FROM %menu% WHERE %menuid% = 0;";

	// D A T A

	/**
	 * Provider context
	 */
	private IProviderContext theContext;

	/**
	 * Node map by id
	 */
	private Hashtable<String, TreeMutableNode> theIdToNodeMap = new Hashtable<String, TreeMutableNode>();

	/**
	 * Properties
	 */
	protected Properties theProperties;

	// C O N S T R U C T O R

	/**
	 * Constructor
	 */
	public Provider()
	{
	}

	/**
	 * Initialize
	 * 
	 * @param thisSource
	 *        source
	 * @return true if successful
	 */
	private boolean initialize(String thisSource)
	{
		// get property file
		String thisSqlFile = thisSource;
		if (thisSqlFile == null)
			thisSqlFile = theContext.getParameter("sql");
		if (thisSqlFile == null)
			thisSqlFile = thePropertyFile;

		// parse property file
		if (thisSqlFile != null)
		{
			// give the context a chance
			URL thisUrl = theContext.makeURL(thisSqlFile);
			if (thisUrl == null)
				return false;

			theProperties = SqlProperties.load(thisUrl);
			if (theProperties != null)
				return true;
		}
		return false;
	}

	// I N T E R F A C E

	/*
	 * (non-Javadoc)
	 * @see jFamilyTree.model.IProvider#set(jFamilyTree.model.IProviderContext)
	 */
	public void connect(IProviderContext thisContext)
	{
		theContext = thisContext;
	}

	/*
	 * (non-Javadoc)
	 * @see jFamilyTree.model.IProvider#makeModel(java.lang.String)
	 */
	public Model makeModel(String thisSource)
	{
		if (initialize(thisSource))
			return queryModel();
		return null;
	}

	/*
	 * (non-Javadoc)
	 * @see jFamilyTree.model.IProvider#makeTree(java.lang.String, boolean)
	 */
	public Tree makeTree(String thisSource, boolean checkRecursion)
	{
		if (initialize(thisSource))
			return queryTree();
		return null;
	}

	// H E L P E R

	/**
	 * Make URL
	 * 
	 * @param theseProperties
	 *        properties
	 * @return url string
	 */
	private String makeUrl(Properties theseProperties)
	{
		String thisURL = theseProperties.getProperty("url");
		if (thisURL == null || thisURL.isEmpty())
		{
			String thisProtocol = theseProperties.getProperty("protocol");
			String thisServer = theseProperties.getProperty("server");
			if (thisServer == null || thisServer.isEmpty())
				thisServer = "localhost";
			String thisDatabase = theseProperties.getProperty("db");
			thisURL = thisProtocol + "://" + thisServer + "/" + thisDatabase;
		}
		return thisURL;
	}

	// Q U E R Y

	/**
	 * Query model
	 * 
	 * @return model
	 */
	private Model queryModel()
	{
		// url/user/password
		String thisUrl = makeUrl(theProperties);
		String thisUser = theProperties.getProperty("user");
		String thisPasswd = theProperties.getProperty("passwd");

		try
		{
			// connect
			Connection thisConnection = DriverManager.getConnection(thisUrl, thisUser, thisPasswd);

			// tree
			Tree thisTree = queryTree(thisConnection);

			// settings
			Settings theseSettings = querySettings(thisConnection);

			// close connection
			thisConnection.close();

			// result
			if (thisTree == null)
				return null;
			return new Model(thisTree, theseSettings);
		}
		catch (SQLException thisException)
		{
			System.err.println("Sql: " + thisException.getMessage());
		}
		return null;
	}

	/**
	 * Query tree
	 * 
	 * @return tree
	 */
	private Tree queryTree()
	{
		// url/user/password
		String thisUrl = makeUrl(theProperties);
		String thisUser = theProperties.getProperty("user");
		String thisPasswd = theProperties.getProperty("passwd");

		try
		{
			// connect
			Connection thisConnection = DriverManager.getConnection(thisUrl, thisUser, thisPasswd);

			// tree
			Tree thisTree = queryTree(thisConnection);

			// close connection
			thisConnection.close();

			// result
			return thisTree;
		}
		catch (SQLException thisException)
		{
			System.err.println("Sql: " + thisException.getMessage());
		}
		return null;
	}

	/**
	 * Query tree
	 * 
	 * @param thisConnection
	 *        connection
	 * @return tree
	 * @throws SQLException
	 */
	private Tree queryTree(Connection thisConnection) throws SQLException
	{
		INode thisRoot = queryNodes(thisConnection);
		List<IEdge> theseEdges = queryEdges(thisConnection);
		return new Tree(thisRoot, theseEdges);
	}

	/**
	 * Query nodes
	 * 
	 * @param thisConnection
	 *        connection
	 * @return tree root node
	 * @throws SQLException
	 */
	private MutableNode queryNodes(Connection thisConnection) throws SQLException
	{
		// sql
		String thisNodesSql = theProperties.getProperty("nodesSql", theDefaultNodesSql);
		thisNodesSql = macroExpand(thisNodesSql);

		String thisTreeEdgesSql = theProperties.getProperty("treeEdgesSql", theDefaultTreeEdgesSql);
		thisTreeEdgesSql = macroExpand(thisTreeEdgesSql);

		// field names
		String thisIdName = getName("nodes.id");
		String thisLabelName = getName("nodes.label");
		String thisContentName = getName("nodes.content");
		String thisBackcolorName = getName("nodes.backcolor");
		String thisForecolorName = getName("nodes.forecolor");
		String thisImageName = getName("nodes.image");
		String thisLinkName = getName("nodes.link");
		String thisTargetName = getName("nodes.target");
		String thisWeightName = getName("nodes.weight");
		String thisMountpointName = getName("nodes.mountpoint");

		String thisFromName = getName("edges.from");
		String thisToName = getName("edges.to");
		String thisEdgeLabelName = getName("edges.label");
		String thisEdgeImageName = getName("edges.image");
		String thisEdgeColorName = getName("edges.color");
		String thisEdgeHiddenName = getName("edges.hidden");
		String thisEdgeStrokeName = getName("edges.stroke");
		String thisEdgeFromTerminatorName = getName("edges.fromterminator");
		String thisEdgeToTerminatorName = getName("edges.toterminator");

		// id to node map
		theIdToNodeMap.clear();

		// N O D E S
		Statement thisNodesStatement = thisConnection.createStatement();
		ResultSet thisNodesResultSet = thisNodesStatement.executeQuery(thisNodesSql);
		while (thisNodesResultSet.next())
		{
			// read id
			String thisId = thisNodesResultSet.getString(thisIdName);
			if (thisId == null)
			{
				System.err.println("Sql: " + thisNodesResultSet.getRow() + "th edge record with null id");
				continue;
			}

			// make node
			TreeMutableNode thisNode = new TreeMutableNode(null, thisId);
			theIdToNodeMap.put(thisId, thisNode);

			// data
			thisNode.setLabel(readString(thisNodesResultSet, thisLabelName));
			thisNode.setContent(readString(thisNodesResultSet, thisContentName));
			thisNode.setBackColor(readColor(thisNodesResultSet, thisBackcolorName));
			thisNode.setForeColor(readColor(thisNodesResultSet, thisForecolorName));
			thisNode.setImageFile(readString(thisNodesResultSet, thisImageName));
			thisNode.setLink(readString(thisNodesResultSet, thisLinkName));
			thisNode.setTarget(readString(thisNodesResultSet, thisTargetName));
			Double thisWeight = readDouble(thisNodesResultSet, thisWeightName);
			if (thisWeight != null)
				thisNode.setWeight(-thisWeight);

			// mountpoint
			String thisValue = readString(thisNodesResultSet, thisMountpointName);
			if (thisValue != null)
			{
				MountPoint.Mounting thisMountPoint = new MountPoint.Mounting();
				thisMountPoint.theURL = thisValue;
				thisNode.setMountPoint(thisMountPoint);
				Boolean thisBooleanValue = readBoolean(thisNodesResultSet, "now");
				if (thisBooleanValue != null && thisBooleanValue)
				{
					System.err.println("SQL: Recursive mounting not implemented");
				}
			}
		}
		thisNodesResultSet.close();
		thisNodesStatement.close();

		// T R E E . E D G E S
		List<TreeMutableNode> theseParentLessNodes = new ArrayList<TreeMutableNode>();
		Statement thisTreeEdgesStatement = thisConnection.createStatement();
		ResultSet thisTreeEdgesResultSet = thisTreeEdgesStatement.executeQuery(thisTreeEdgesSql);
		while (thisTreeEdgesResultSet.next())
		{
			// from/to ids
			String thisFromId = thisTreeEdgesResultSet.getString(thisFromName);
			String thisToId = thisTreeEdgesResultSet.getString(thisToName);
			if ((thisFromId == null || thisFromId.isEmpty()) && (thisToId == null || thisToId.isEmpty()))
			{
				System.err.println("Sql: " + thisTreeEdgesResultSet.getRow() + "th tree edge record with null ids : from-id = <" + thisFromId + "> and to-id=<" + thisToId + ">");
				continue;
			}

			// to node
			TreeMutableNode thisToNode = theIdToNodeMap.get(thisToId);

			// root candidate
			if ((thisFromId == null || thisFromId.isEmpty()) && thisToNode != null)
			{
				theseParentLessNodes.add(thisToNode);
				continue;
			}

			// from node
			TreeMutableNode thisFromNode = theIdToNodeMap.get(thisFromId);

			// ill-formed nodes
			if (thisFromNode == null || thisToNode == null)
			{
				System.err.println("Sql: " + thisTreeEdgesResultSet.getRow() + "th tree edge record with null end nodes : from-id = <" + thisFromId + "> and to-id=<" + thisToId + ">");
				continue;
			}

			// make tree
			List<INode> theseChildren = thisFromNode.getChildren();
			if (theseChildren == null)
			{
				theseChildren = new ArrayList<INode>();
				thisFromNode.setChildren(theseChildren);
			}
			theseChildren.add(thisToNode);
			thisToNode.setParent(thisFromNode);

			// data
			thisToNode.setEdgeLabel(readString(thisTreeEdgesResultSet, thisEdgeLabelName));
			thisToNode.setEdgeImageFile(readString(thisTreeEdgesResultSet, thisEdgeImageName));
			thisToNode.setEdgeColor(readColor(thisTreeEdgesResultSet, thisEdgeColorName));
			Boolean thisBoolean = readBoolean(thisTreeEdgesResultSet, thisEdgeHiddenName);
			thisToNode.setEdgeStyle(Utils.parseStyle(readString(thisTreeEdgesResultSet, thisEdgeStrokeName), readString(thisTreeEdgesResultSet, thisEdgeFromTerminatorName), readString(thisTreeEdgesResultSet, thisEdgeToTerminatorName),
					thisBoolean == null ? null : thisBoolean.toString()));
		}
		thisTreeEdgesResultSet.close();
		thisTreeEdgesStatement.close();

		if (theseParentLessNodes.size() == 1)
			return theseParentLessNodes.get(0);
		return theIdToNodeMap.get(getName("id.root"));
	}

	/**
	 * Query edges
	 * 
	 * @param thisConnection
	 *        connection
	 * @return edge list
	 * @throws SQLException
	 */
	private List<IEdge> queryEdges(Connection thisConnection) throws SQLException
	{
		// sql
		String thisEdgesSql = theProperties.getProperty("edgesSql", theDefaultEdgesSql);
		thisEdgesSql = macroExpand(thisEdgesSql);

		// field names
		String thisFromIdName = getName("edges.from");
		String thisToIdName = getName("edges.to");
		String thisEdgeLabelName = getName("edges.label");
		String thisEdgeImageName = getName("edges.image");
		String thisEdgeColorName = getName("edges.color");
		String thisEdgeHiddenName = getName("edges.hidden");
		String thisEdgeStrokeName = getName("edges.stroke");
		String thisEdgeFromTerminatorName = getName("edges.fromterminator");
		String thisEdgeToTerminatorName = getName("edges.toterminator");

		List<IEdge> thisEdgeList = null;

		// EDGES
		Statement thisEdgesStatement = thisConnection.createStatement();
		ResultSet thisEdgesRecordSet = thisEdgesStatement.executeQuery(thisEdgesSql);
		while (thisEdgesRecordSet.next())
		{
			// read from/to ids
			String thisFromId = thisEdgesRecordSet.getString(thisFromIdName);
			String thisToId = thisEdgesRecordSet.getString(thisToIdName);
			if (thisFromId == null || thisToId == null)
			{
				System.err.println("Sql: " + thisEdgesRecordSet.getRow() + "th edge record with null from/to id");
				continue;
			}

			// check from/to
			MutableNode thisFromNode = theIdToNodeMap.get(thisFromId);
			MutableNode thisToNode = theIdToNodeMap.get(thisToId);
			if (thisFromNode == null || thisToNode == null)
			{
				System.err.println("Sql: " + thisEdgesRecordSet.getRow() + "th edge record with null from/to node");
				continue;
			}

			// make edge
			MutableEdge thisEdge = new MutableEdge(thisFromNode, thisToNode);
			thisEdge.setColor(readColor(thisEdgesRecordSet, "color"));
			if (thisEdgeList == null)
				thisEdgeList = new ArrayList<IEdge>();
			thisEdgeList.add(thisEdge);

			// attributes
			thisEdge.setLabel(readString(thisEdgesRecordSet, thisEdgeLabelName));
			thisEdge.setImageFile(readString(thisEdgesRecordSet, thisEdgeImageName));
			thisEdge.setColor(readColor(thisEdgesRecordSet, thisEdgeColorName));
			Boolean thisBoolean = readBoolean(thisEdgesRecordSet, thisEdgeHiddenName);
			thisEdge.setStyle(Utils.parseStyle(thisEdgesRecordSet.getString(thisEdgeStrokeName), thisEdgesRecordSet.getString(thisEdgeFromTerminatorName), thisEdgesRecordSet.getString(thisEdgeToTerminatorName), thisBoolean == null ? null : thisBoolean
					.toString()));
		}
		thisEdgesRecordSet.close();
		thisEdgesStatement.close();
		return thisEdgeList;
	}

	/**
	 * Query settings
	 * 
	 * @param thisConnection
	 *        connection
	 * @return settings
	 */
	private Settings querySettings(Connection thisConnection)
	{
		// sql
		String thisSettingsSql = theProperties.getProperty("settingsSql", theDefaultSettingsSql);
		thisSettingsSql = macroExpand(thisSettingsSql);
		String thisMenuSql = theProperties.getProperty("menuSql", theDefaultMenuSql);
		thisMenuSql = macroExpand(thisMenuSql);

		// field names
		String thisBackImageName = getName("settings.backimage");
		String thisBackcolorName = getName("settings.backcolor");
		String thisForecolorName = getName("settings.forecolor");
		String thisFontFaceName = getName("settings.fontface");
		String thisFontSizeName = getName("settings.fontsize");
		String thisFontSizeStepName = getName("settings.fontsizestep");
		String thisOrientationName = getName("settings.orientation");
		String thisExpansionName = getName("settings.expansion");
		String thisSweepName = getName("settings.sweep");
		String thisPreserveOrientationName = getName("settings.preserveorientation");
		String thisHyperbolicName = getName("settings.hyperbolic");
		String thisHasToolbarName = getName("settings.hastoolbar");
		String thisHasStatusbarName = getName("settings.hasstatusbar");
		String thisHasPopupMenuName = getName("settings.haspopupmenu");
		String thisHasTooltipName = getName("settings.hastooltip");
		String thisTooltipDisplaysContentName = getName("settings.tooltipdisplayscontent");
		String thisFocusOnHoverName = getName("settings.focusonhover");
		String thisFocusName = getName("settings.focus");
		String thisXMoveToName = getName("settings.xmoveto");
		String thisYMoveToName = getName("settings.ymoveto");
		String thisXShiftName = getName("settings.xshift");
		String thisYShiftName = getName("settings.yshift");
		String thisNodeBackcolorName = getName("settings.nodebackcolor");
		String thisNodeForecolorName = getName("settings.nodeforecolor");
		String thisNodeImageName = getName("settings.nodeimage");
		String thisTreeEdgeColorName = getName("settings.treeedgecolor");
		String thisTreeEdgeHiddenName = getName("settings.treeedgehidden");
		String thisTreeEdgeStrokeName = getName("settings.treeedgestroke");
		String thisTreeEdgeFromTerminatorName = getName("settings.treeedgefromterminator");
		String thisTreeEdgeToTerminatorName = getName("settings.treeedgetoterminator");
		String thisTreeEdgeImageName = getName("settings.treeedgeimage");
		String thisEdgeColorName = getName("settings.edgecolor");
		String thisEdgeHiddenName = getName("settings.edgehidden");
		String thisEdgeStrokeName = getName("settings.edgestroke");
		String thisEdgeFromTerminatorName = getName("settings.edgefromterminator");
		String thisEdgeToTerminatorName = getName("settings.edgetoterminator");
		String thisEdgeImageName = getName("settings.edgeimage");

		String thisMenuActionName = getName("menu.action");
		String thisMenuLabelName = getName("menu.label");
		String thisMenuTargetName = getName("menu.target");
		String thisMenuScopeName = getName("menu.scope");
		String thisMenuModeName = getName("menu.mode");
		String thisMenuLinkName = getName("menu.link");

		try
		{
			Settings theseSettings = new Settings();

			// first record
			Statement thisEdgesStatement = thisConnection.createStatement();
			ResultSet thisSettingsResultSetSet = thisEdgesStatement.executeQuery(thisSettingsSql);
			while (thisSettingsResultSetSet.next())
			{
				// boolean
				theseSettings.theHasToolbarFlag = readBoolean(thisSettingsResultSetSet, thisHasToolbarName);
				theseSettings.theHasStatusbarFlag = readBoolean(thisSettingsResultSetSet, thisHasStatusbarName);
				theseSettings.theHasPopUpMenuFlag = readBoolean(thisSettingsResultSetSet, thisHasPopupMenuName);
				theseSettings.theHasToolTipFlag = readBoolean(thisSettingsResultSetSet, thisHasTooltipName);
				theseSettings.theToolTipDisplaysContentFlag = readBoolean(thisSettingsResultSetSet, thisTooltipDisplaysContentName);
				theseSettings.theFocusOnHoverFlag = readBoolean(thisSettingsResultSetSet, thisFocusOnHoverName);
				theseSettings.thePreserveOrientationFlag = readBoolean(thisSettingsResultSetSet, thisPreserveOrientationName);
				theseSettings.theHyperbolicEdgesFlag = readBoolean(thisSettingsResultSetSet, thisHyperbolicName);

				// integer
				theseSettings.theFontSize = readInteger(thisSettingsResultSetSet, thisFontSizeName);
				theseSettings.theFontSizeStep = readInteger(thisSettingsResultSetSet, thisFontSizeStepName);

				// double
				theseSettings.theXMoveTo = readDouble(thisSettingsResultSetSet, thisXMoveToName);
				theseSettings.theYMoveTo = readDouble(thisSettingsResultSetSet, thisYMoveToName);
				theseSettings.theXShift = readDouble(thisSettingsResultSetSet, thisXShiftName);
				theseSettings.theYShift = readDouble(thisSettingsResultSetSet, thisYShiftName);
				theseSettings.theExpansion = readDouble(thisSettingsResultSetSet, thisExpansionName);
				theseSettings.theSweep = readDouble(thisSettingsResultSetSet, thisSweepName);

				// colors
				theseSettings.theBackColor = readColor(thisSettingsResultSetSet, thisBackcolorName);
				theseSettings.theForeColor = readColor(thisSettingsResultSetSet, thisForecolorName);
				theseSettings.theNodeBackColor = readColor(thisSettingsResultSetSet, thisNodeBackcolorName);
				theseSettings.theNodeForeColor = readColor(thisSettingsResultSetSet, thisNodeForecolorName);
				theseSettings.theTreeEdgeColor = readColor(thisSettingsResultSetSet, thisTreeEdgeColorName);
				theseSettings.theEdgeColor = readColor(thisSettingsResultSetSet, thisEdgeColorName);

				// styles
				Boolean thisBoolean = readBoolean(thisSettingsResultSetSet, thisTreeEdgeHiddenName);
				theseSettings.theTreeEdgeStyle = Utils.parseStyle(readString(thisSettingsResultSetSet, thisTreeEdgeStrokeName), readString(thisSettingsResultSetSet, thisTreeEdgeFromTerminatorName), readString(thisSettingsResultSetSet,
						thisTreeEdgeToTerminatorName), thisBoolean == null ? null : thisBoolean.toString());
				thisBoolean = readBoolean(thisSettingsResultSetSet, thisEdgeHiddenName);
				theseSettings.theEdgeStyle = Utils.parseStyle(readString(thisSettingsResultSetSet, thisEdgeStrokeName), readString(thisSettingsResultSetSet, thisEdgeFromTerminatorName), readString(thisSettingsResultSetSet, thisEdgeToTerminatorName),
						thisBoolean == null ? null : thisBoolean.toString());

				// string
				theseSettings.theOrientation = readString(thisSettingsResultSetSet, thisOrientationName);
				theseSettings.theFocus = readString(thisSettingsResultSetSet, thisFocusName);
				theseSettings.theFontFace = readString(thisSettingsResultSetSet, thisFontFaceName);
				theseSettings.theOrientation = readString(thisSettingsResultSetSet, thisOrientationName);
				theseSettings.theBackgroundImage = readString(thisSettingsResultSetSet, thisBackImageName);
				theseSettings.theDefaultNodeImage = readString(thisSettingsResultSetSet, thisNodeImageName);
				theseSettings.theDefaultTreeEdgeImage = readString(thisSettingsResultSetSet, thisTreeEdgeImageName);
				theseSettings.theDefaultEdgeImage = readString(thisSettingsResultSetSet, thisEdgeImageName);

				// take first if many are returned
				break;
			}
			thisSettingsResultSetSet.close();
			thisEdgesStatement.close();

			// first record
			Statement thisMenuStatement = thisConnection.createStatement();
			ResultSet thisMenuResultSet = thisMenuStatement.executeQuery(thisMenuSql);
			while (thisMenuResultSet.next())
			{
				MenuItem thisMenuItem = new MenuItem();
				thisMenuItem.theLabel = readString(thisMenuResultSet, thisMenuLabelName);
				thisMenuItem.theMatchTarget = readString(thisMenuResultSet, thisMenuTargetName);
				thisMenuItem.theLink = readString(thisMenuResultSet, thisMenuLinkName);
				String thisActionString = readString(thisMenuResultSet, thisMenuActionName);
				String thisScopeString = readString(thisMenuResultSet, thisMenuScopeName);
				String thisModeString = readString(thisMenuResultSet, thisMenuModeName);
				Utils.parseMenuItem(thisMenuItem, thisActionString, thisScopeString, thisModeString);

				// add
				if (theseSettings.theMenu == null)
					theseSettings.theMenu = new ArrayList<MenuItem>();
				theseSettings.theMenu.add(thisMenuItem);
			}
			thisMenuResultSet.close();
			thisMenuStatement.close();

			return theseSettings;
		}
		catch (SQLException e)
		{
			System.err.println("Sql: Cannot read Settings." + e.getMessage());
		}
		// return default
		return new Settings();
	}

	// R E S U L T S E T . H E L P E R S

	/**
	 * Read string
	 * 
	 * @param thisRecordSet
	 *        record set
	 * @param thisName
	 *        field name
	 * @return String value
	 */
	private String readString(ResultSet thisRecordSet, String thisName)
	{
		try
		{
			return thisRecordSet.getString(thisName);
		}
		catch (SQLException e)
		{
		}
		return null;
	}

	/**
	 * Read integer
	 * 
	 * @param thisRecordSet
	 *        record set
	 * @param thisName
	 *        field name
	 * @return Integer value
	 */
	private Integer readInteger(ResultSet thisRecordSet, String thisName)
	{
		try
		{
			return thisRecordSet.getInt(thisName);
		}
		catch (SQLException e)
		{
		}
		return null;
	}

	/**
	 * Read double
	 * 
	 * @param thisRecordSet
	 *        record set
	 * @param thisName
	 *        field name
	 * @return Double value
	 */
	private Double readDouble(ResultSet thisRecordSet, String thisName)
	{
		try
		{
			return thisRecordSet.getDouble(thisName);
		}
		catch (SQLException e)
		{
		}
		return null;
	}

	/**
	 * Read boolean
	 * 
	 * @param thisRecordSet
	 *        record set
	 * @param thisName
	 *        field name
	 * @return Boolean value
	 */
	private Boolean readBoolean(ResultSet thisRecordSet, String thisName)
	{
		try
		{
			return thisRecordSet.getBoolean(thisName);
		}
		catch (SQLException e)
		{
		}
		return null;
	}

	/**
	 * Read color
	 * 
	 * @param thisRecordSet
	 *        record set
	 * @param thisName
	 *        field name
	 * @return Color value
	 */
	private Color readColor(ResultSet thisRecordSet, String thisName)
	{
		try
		{
			String thisValue = thisRecordSet.getString(thisName);
			if (thisValue != null)
				return Utils.stringToColor(thisValue);
		}
		catch (SQLException e)
		{
		}
		return null;
	}

	// P R O P E R T I E S

	/**
	 * Get name
	 * 
	 * @param thisKey
	 *        key
	 * @return value if not null or key after last '.'
	 */
	private String getName(String thisKey)
	{
		String thisValue = (String) theProperties.get(thisKey);
		if (thisValue != null)
			return thisValue;

		String thisStringKey = (String) thisKey;
		int thisIndex = thisStringKey.lastIndexOf('.');
		return thisIndex == -1 ? thisStringKey : thisStringKey.substring(thisIndex + 1);
	}

	/**
	 * Expand macros
	 * 
	 * @param thisString
	 *        string to expand macro in
	 * @return string with expanded macros
	 */
	private String macroExpand(String thisString)
	{
		final String thisRegEx = "%[^%]*%";
		final Pattern thePattern = Pattern.compile(thisRegEx);

		// percents
		int thisPercentCount = 0;
		for (int p = 0; (p = thisString.indexOf('"', p + 1)) != -1;)
		{
			thisPercentCount++;
		}
		if (thisPercentCount % 2 != 0)
		{
			return null;
		}

		// macro map
		Map<String, String> thisMacroMap = new HashMap<String, String>();
		for (Matcher thisMatcher = thePattern.matcher(thisString); thisMatcher.find();)
		{
			String thisMatch = thisMatcher.group();
			String thisKey = thisMatch.substring(1, thisMatch.length() - 1);
			if (!thisMacroMap.containsKey(thisKey))
			{
				String thisValue = (String) theProperties.get(thisKey);
				thisMacroMap.put(thisKey, thisValue);
			}
		}

		// macro substitution
		String thisResult = thisString;
		for (String thisKey : thisMacroMap.keySet())
		{
			String thisValue = thisMacroMap.get(thisKey);
			if (thisValue == null)
				thisValue = thisKey;
			thisResult = thisResult.replaceAll("%" + thisKey + "%", thisValue);
		}
		return thisResult;
	}
}
